﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;


namespace crmbl.Azure.Globals
{
    public class SqlHelper : IDisposable
    {
        private SqlConnection _sqlCon;

       
        #region Constructors

        public SqlHelper(String connectionString)
        {
            
            _sqlCon = new SqlConnection(connectionString);

            _sqlCon.Open();
        }

        #endregion

        public void RunBatchScript(String text)
        {
            foreach (String s in text.Split(new String[] { "\r\nGO\r\n" }, StringSplitOptions.RemoveEmptyEntries))
            {
                using (SqlCommand cmd = new SqlCommand(s, _sqlCon))
                {
                    cmd.ExecuteNonQuery();
                }
            }
        }

        #region Internal Methods

        private SqlCommand GenerateSqlCommand(String queryText, Dictionary<String, Object> parameters = null, Boolean isStoredProcedure = false)
        {
            SqlCommand cmd = new SqlCommand(queryText, _sqlCon);

            if (isStoredProcedure)
                cmd.CommandType = CommandType.StoredProcedure;

            if (parameters != null)
            {
                foreach (KeyValuePair<String, Object> kvp in parameters)
                {
                    cmd.Parameters.AddWithValue(kvp.Key, kvp.Value);
                }
            }

            return cmd;
        }

        #endregion

        #region Public Query Execution Methods


        public void RunBulkInsert(String tableName, DataTable data, Boolean mapColumnsByNames = true)
        {
            SqlBulkCopy bulkCopy = new SqlBulkCopy(_sqlCon);

            if (mapColumnsByNames)
            {
                foreach (DataColumn c in data.Columns)
                {
                    bulkCopy.ColumnMappings.Add(c.ColumnName, c.ColumnName);
                }
            }

            bulkCopy.DestinationTableName = tableName;

            bulkCopy.WriteToServer(data);
        }

        public SqlDataReader RunQueryGetSqlDataReader(String queryText, Dictionary<String, object> parameters = null, Boolean isStoredProcedure = false)
        {
            using (SqlCommand cmd = GenerateSqlCommand(queryText, parameters, isStoredProcedure))
            {
                return cmd.ExecuteReader();
            }
        }

        public int RunQueryGetRowsAffected(String queryText, Dictionary<String, object> parameters = null, Boolean isStoredProcedure = false)
        {
            using (SqlCommand cmd = GenerateSqlCommand(queryText, parameters, isStoredProcedure))
            {
                return cmd.ExecuteNonQuery();
            }
        }

        public object RunQueryGetScalar(String queryText, Dictionary<String, object> parameters = null, Boolean isStoredProcedure = false)
        {
            using (SqlCommand cmd = GenerateSqlCommand(queryText, parameters, isStoredProcedure))
            {
                return cmd.ExecuteScalar();
            }
        }

        public DataTable RunQueryGetDataTable(String queryText, Dictionary<String, object> parameters = null, Boolean isStoredProcedure = false)
        {
            using (SqlCommand cmd = GenerateSqlCommand(queryText, parameters, isStoredProcedure))
            {
                DataTable dt = new DataTable();

                SqlDataAdapter adapt = new SqlDataAdapter(cmd);
                adapt.Fill(dt);

                return dt;
            }
        }


        #endregion

        #region Parameter Helpers

        public static Dictionary<String, object> GenerateParameterDictionary(String paramName1, object paramValue1, String paramName2 = null, object paramValue2 = null, String paramName3 = null, object paramValue3 = null, String paramName4 = null, object paramValue4 = null, String paramName5 = null, object paramValue5 = null, String paramName6 = null, object paramValue6 = null)
        {
            Dictionary<string, object> d = new Dictionary<string, object>();

            d.Add(paramName1, paramValue1);

            if (paramName2 != null && paramValue2 != null)
            {
                d.Add(paramName2, paramValue2);
            }
            else
            {
                return d;
            }

            if (paramName3 != null && paramValue3 != null)
                d.Add(paramName3, paramValue3);

            if (paramName4 != null && paramValue4 != null)
                d.Add(paramName4, paramValue4);

            if (paramName5 != null && paramValue5 != null)
                d.Add(paramName5, paramValue5);

            if (paramName6 != null && paramValue6 != null)
                d.Add(paramName6, paramValue6);

            return d;
        }


        #endregion

        #region IDisposable Methods

        public void Dispose()
        {
            try
            {
                _sqlCon.Close();
            }
            catch (Exception ex)
            {
                // ignore exceptions
            }
        }

        #endregion
    }
}
